﻿﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.Common;

namespace JinianNet.KuaiBlog.SQLServerDAL
{
    public abstract class DbHelper
    {
        #region
        public void SetTimeoutDefault()
        {
            Timeout = 30;
        }
        private int Timeout = 30;
        private string _connectionstring;
        private DbConnection _connection;

        public string ConnectionString
        {
            get { return _connectionstring; }
            set { _connectionstring = value; }
        }

        public DbConnection Connection
        {
            get { return _connection; }
            private set { _connection = value; }
        }

        public DbHelper()
            : this(null)
        {

        }

        public DbHelper(string connectionString)
        {
            this.ConnectionString = connectionString;
        }
        #endregion
        #region 实现
        protected abstract DbConnection CreateConnection();
        protected abstract DbCommand CreateCommand();
        protected abstract DbDataAdapter CreateDataAdapter();
        protected abstract DbParameter CreateParameter();
        #endregion
        #region 执行命令
        public int ExecuteNonQuery(string cmdText, params object[] commandParameters)
        {
            if (commandParameters != null && commandParameters.Length > 0)
            {
                cmdText = ParseCommandText(cmdText, commandParameters);
                return ExecuteNonQuery(CommandType.Text, cmdText, PrepareParameter(commandParameters));
            }
            else
            {
                return ExecuteNonQuery(CommandType.Text, cmdText, null);
            }
        }
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            return ExecuteNonQuery(this.ConnectionString, cmdType, cmdText, commandParameters);
        }

        public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {

            DbCommand cmd = CreateCommand();

            using (DbConnection conn = CreateConnection())
            {
                try
                {
                    conn.ConnectionString = connectionString;
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    //this.Connection= conn;
                    return val;
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();

                    }
                    conn.Dispose();
                }
            }
        }

        public int ExecuteNonQuery(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {

            DbCommand cmd = CreateCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            this.Connection = connection;
            return val;
        }

        public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DbCommand cmd = CreateCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            this.Connection = trans.Connection;
            return val;
        }

        #endregion
        #region 执行Reader
        public DbDataReader ExecuteReader(string cmdText, params object[] commandParameters)
        {
            if (commandParameters != null && commandParameters.Length > 0)
            {
                cmdText = ParseCommandText(cmdText, commandParameters);
                return ExecuteReader(CommandType.Text, cmdText, PrepareParameter(commandParameters));
            }
            else
            {
                return ExecuteReader(CommandType.Text, cmdText, null);
            }
        }

        public DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            return ExecuteReader(this.ConnectionString, cmdType, cmdText, commandParameters);
        }

        public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DbCommand cmd = CreateCommand();
            DbConnection conn = CreateConnection();
            conn.ConnectionString = connectionString;
            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                this.Connection = conn;
                return rdr;
            }
            catch
            {

                throw;
            }
            finally
            {
                //conn.Close();
                //conn.Dispose();
                //conn = null;
            }
        }

        public DbDataReader ExecuteReader(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DbCommand cmd = CreateCommand();
            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            //this.Connection = connection;
            return rdr;
        }
        #endregion
        #region 执行Scalar
        public object ExecuteScalar(string cmdText, params object[] commandParameters)
        {
            if (commandParameters != null && commandParameters.Length > 0)
            {
                cmdText = ParseCommandText(cmdText, commandParameters);
                return ExecuteScalar(CommandType.Text, cmdText, PrepareParameter(commandParameters));
            }
            else
            {
                return ExecuteScalar(CommandType.Text, cmdText, null);
            }
        }

        public object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            return ExecuteScalar(this.ConnectionString, cmdType, cmdText, commandParameters);
        }

        public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DbCommand cmd = CreateCommand();

            using (DbConnection connection = CreateConnection())
            {
                try
                {
                    connection.ConnectionString = connectionString;
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    //this.Connection = connection;
                    return val;
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                    connection.Dispose();
                }
            }
        }

        public object ExecuteScalar(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {

            DbCommand cmd = CreateCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            this.Connection = connection;
            return val;
        }

        public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DbCommand cmd = CreateCommand();

            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            this.Connection = trans.Connection;
            return val;
        }
        #endregion
        #region 执行Table
        public DataTable ExecuteTable(string cmdText, params object[] commandParameters)
        {
            if (commandParameters != null && commandParameters.Length > 0)
            {
                cmdText = ParseCommandText(cmdText, commandParameters);
                return ExecuteTable(CommandType.Text, cmdText, PrepareParameter(commandParameters));
            }
            else
            {
                return ExecuteTable(CommandType.Text, cmdText, null);
            }
        }

        public DataTable ExecuteTable(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            return ExecuteTable(this.ConnectionString, cmdType, cmdText, commandParameters);
        }

        public DataTable ExecuteTable(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DbCommand cmd = CreateCommand();

            using (DbConnection connection = CreateConnection())
            {
                try
                {
                    connection.ConnectionString = connectionString;
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    DbDataAdapter ap = CreateDataAdapter();
                    ap.SelectCommand = cmd;
                    DataSet st = new DataSet();
                    ap.Fill(st, "Result");
                    cmd.Parameters.Clear();
                    //this.Connection = connection;
                    return st.Tables["Result"];
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                    connection.Dispose();
                }
            }
        }

        public DataTable ExecuteTable(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {

            DbCommand cmd = CreateCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            DbDataAdapter ap = CreateDataAdapter();
            ap.SelectCommand = cmd;
            DataSet st = new DataSet();
            ap.Fill(st, "Result");
            cmd.Parameters.Clear();
            this.Connection = connection;
            return st.Tables["Result"];
        }
        #endregion
        #region 执行 Transaction

        public int ExecuteTransaction(string[] cmdTexts)
        {
            return ExecuteTransaction(new TransactionCollection(cmdTexts));
        }

        public int ExecuteTransaction(TransactionCollection trans)
        {
            return ExecuteTransaction(this.ConnectionString, trans);
        }

        public int ExecuteTransaction(string connectionString, TransactionCollection trans)
        {

            DbCommand cmd = CreateCommand();

            using (DbConnection conn = CreateConnection())
            {
                try
                {
                    conn.ConnectionString = connectionString;
                    //PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    int val = ExecuteTransaction(conn, trans);
                    cmd.Parameters.Clear();
                    //this.Connection= conn;
                    return val;
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();

                    }
                    conn.Dispose();
                }
            }
        }

        public int ExecuteTransaction(DbConnection connection, TransactionCollection trans)
        {
            int val = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            using (DbTransaction transaction = connection.BeginTransaction())
            {
                try
                {
                    for (int i = 0; i < trans.Count; i++)
                    {
                        val += ExecuteNonQuery(transaction, trans[i].CommandType, trans[i].Text, trans[i].Parameter);
                    }
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    transaction.Dispose();
                }
            }

            return val;
        }
        #endregion
        #region 分页

        /// <summary>
        /// 执行对默认数据库有自定义排序的分页的查询
        /// </summary>
        /// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
        /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
        /// <param name="IndexField"用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id></param>
        /// <param name="OrderFields">排序字段以及方式如：a.OrderId desc,CnName desc</param>
        /// <param name="PageIndex">当前页的页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
        /// <param name="PageCount">输出参数，返回查询的总页数</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回查询结果</returns>
        public DbDataReader ExecuteReaderPage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params DbParameter[] commandParameters)
        {
            return ExecuteReaderPage(this.ConnectionString, SqlAllFields, SqlTablesAndWhere, IndexField, null, OrderFields,PageIndex, PageSize, out RecordCount, out PageCount, commandParameters);
        }
        /// <summary>
        /// 执行对默认数据库有自定义排序的分页的查询
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
        /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
        /// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
        /// <param name="OrderASC">排序方式,如果为true则按升序排序,false则按降序排</param>
        /// <param name="OrderFields">排序字段以及方式如：a.OrderId desc,CnName desc</OrderFields>
        /// <param name="PageIndex">当前页的页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
        /// <param name="PageCount">输出参数，返回查询的总页数</param>
        /// <returns>返回查询结果</returns>
        public DbDataReader ExecuteReaderPage(string connectionString, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string GroupClause, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params DbParameter[] commandParameters)
        {
            DbConnection conn = CreateConnection();
            conn.ConnectionString = connectionString;
            try
            {
                conn.Open();
                DbCommand cmd = CreateCommand();
                PrepareCommand(cmd, conn, null, CommandType.Text, "", commandParameters);
                string Sql = GetPageSql(conn, cmd, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out  RecordCount, out  PageCount);
                if (GroupClause != null && GroupClause.Trim() != "")
                {
                    int n = Sql.ToLower().LastIndexOf(" order by ");
                    Sql = Sql.Substring(0, n) + " " + GroupClause + " " + Sql.Substring(n);
                }
                cmd.CommandText = Sql;
                DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                //this.Connection = conn;
                return rdr;
            }
            catch
            {
                //if (conn.State == ConnectionState.Open)
                //    conn.Close();
                throw;
            }
            finally
            {
                //if (conn.State == ConnectionState.Open)
                //    conn.Close();
                //conn.Dispose();
                //conn = null;
            }
        }

        /// <summary>
        /// 执行对默认数据库有自定义排序的分页的查询
        /// </summary>
        /// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
        /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
        /// <param name="IndexField"用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id></param>
        /// <param name="OrderFields">排序字段以及方式如：a.OrderId desc,CnName desc</param>
        /// <param name="PageIndex">当前页的页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回查询结果</returns>
        public DbDataReader ExecuteReaderPage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, params DbParameter[] commandParameters)
        {
            return ExecuteReaderPage(this.ConnectionString, SqlAllFields, SqlTablesAndWhere, IndexField, null, OrderFields, PageIndex, PageSize, commandParameters);
        }

        public DbDataReader ExecuteReaderPage(string connectionString, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string GroupClause, string OrderFields, int PageIndex, int PageSize, params DbParameter[] commandParameters)
        {
            DbConnection conn = CreateConnection();
            conn.ConnectionString = connectionString;
            try
            {
                conn.Open();
                DbCommand cmd = CreateCommand();
                PrepareCommand(cmd, conn, null, CommandType.Text, "", commandParameters);
                string Sql = GetPageSql(SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize);
                if (GroupClause != null && GroupClause.Trim() != "")
                {
                    int n = Sql.ToLower().LastIndexOf(" order by ");
                    Sql = Sql.Substring(0, n) + " " + GroupClause + " " + Sql.Substring(n);
                }
                cmd.CommandText = Sql;
                DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                //this.Connection = conn;
                return rdr;
            }
            catch
            {
                //if (conn.State == ConnectionState.Open)
                //    conn.Close();
                throw;
            }
            finally
            {
                //if (conn.State == ConnectionState.Open)
                //    conn.Close();
                //conn.Dispose();
                //conn = null;
            }
        }
        /// <summary>
        /// 执行对默认数据库有自定义排序的分页的查询
        /// </summary>
        /// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
        /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
        /// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
        /// <param name="OrderASC">排序方式,如果为true则按升序排序,false则按降序排</param>
        /// <param name="OrderFields">排序字段以及方式如：a.OrderId desc,CnName desc</OrderFields>
        /// <param name="PageIndex">当前页的页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
        /// <param name="PageCount">输出参数，返回查询的总页数</param>
        /// <returns>返回查询结果</returns>
        public DataTable ExecutePage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params DbParameter[] commandParameters)
        {
            return ExecutePage(this.ConnectionString, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out  RecordCount, out  PageCount, commandParameters);
        }
        public DataTable ExecutePage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, params DbParameter[] commandParameters)
        {
            return ExecutePage(this.ConnectionString, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, commandParameters);
        }
        /// <summary>
        /// 执行有自定义排序的分页的查询
        /// </summary>
        /// <param name="connectionString">SQL数据库连接字符串</param>
        /// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
        /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
        /// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
        /// <param name="OrderASC">排序方式,如果为true则按升序排序,false则按降序排</param>
        /// <param name="OrderFields">排序字段以及方式如：a.OrderId desc,CnName desc</OrderFields>
        /// <param name="PageIndex">当前页的页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
        /// <param name="PageCount">输出参数，返回查询的总页数</param>
        /// <returns>返回查询结果</returns>
        public DataTable ExecutePage(string connectionString, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params DbParameter[] commandParameters)
        {
            using (DbConnection connection = CreateConnection())
            {
                try
                {
                    connection.ConnectionString = connectionString;
                    connection.Open();
                    //this.Connection= connection;
                    return ExecutePage(connection, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, commandParameters);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                        connection.Dispose();
                    }
                }
            }
        }
        public DataTable ExecutePage(string connectionString, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, params DbParameter[] commandParameters)
        {
            using (DbConnection connection = CreateConnection())
            {
                try
                {
                    connection.ConnectionString = connectionString;
                    connection.Open();
                    //this.Connection= connection;
                    return ExecutePage(connection, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, commandParameters);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                        connection.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// 执行有自定义排序的分页的查询
        /// </summary>
        /// <param name="connection">SQL数据库连接对象</param>
        /// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
        /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
        /// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
        /// <param name="OrderASC">排序方式,如果为true则按升序排序,false则按降序排</param>
        /// <param name="OrderFields">排序字段以及方式如：a.OrderId desc,CnName desc</OrderFields>
        /// <param name="PageIndex">当前页的页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
        /// <param name="PageCount">输出参数，返回查询的总页数</param>
        /// <returns>返回查询结果</returns>
        public DataTable ExecutePage(DbConnection connection, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params DbParameter[] commandParameters)
        {
            DbCommand cmd = CreateCommand();
            PrepareCommand(cmd, connection, null, CommandType.Text, "", commandParameters);
            string Sql = GetPageSql(connection, cmd, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out  RecordCount, out  PageCount);
            cmd.CommandText = Sql;
            DbDataAdapter ap = CreateDataAdapter();
            ap.SelectCommand = cmd;
            DataSet st = new DataSet();
            ap.Fill(st, "PageResult");
            cmd.Parameters.Clear();
            this.Connection = connection;
            return st.Tables["PageResult"];
        }
        public DataTable ExecutePage(DbConnection connection, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, params DbParameter[] commandParameters)
        {
            DbCommand cmd = CreateCommand();
            PrepareCommand(cmd, connection, null, CommandType.Text, "", commandParameters);
            string Sql = GetPageSql(SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize);
            cmd.CommandText = Sql;
            DbDataAdapter ap = CreateDataAdapter();
            ap.SelectCommand = cmd;
            DataSet st = new DataSet();
            ap.Fill(st, "PageResult");
            cmd.Parameters.Clear();
            this.Connection = connection;
            return st.Tables["PageResult"];
        }


        public string GetPageSql(DbConnection connection, DbCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
        {
            RecordCount = 0;
            PageCount = 0;
            if (PageSize <= 0)
            {
                PageSize = 10;
            }
            string SqlCount = string.Format("select count({0}) from {1}", IndexField, SqlTablesAndWhere);
            cmd.CommandText = SqlCount;
            RecordCount = Convert.ToInt32(cmd.ExecuteScalar());
            if (RecordCount % PageSize == 0)
            {
                PageCount = RecordCount / PageSize;
            }
            else
            {
                PageCount = RecordCount / PageSize + 1;
            }
            if (PageIndex > PageCount)
                PageIndex = PageCount;
            if (PageIndex < 1)
                PageIndex = 1;
            return GetPageSql(SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize);
        }

        public abstract string GetPageSql(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize);

        #region 针对不同的数据库设计不同的SQL语句
        ///// <summary>
        ///// 适用SQL Server/ACCESS的分页语句
        ///// </summary>
        //private string ReutrnSqlPageSql(DbConnection connection, DbCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
        //{
        //    RecordCount = 0;
        //    PageCount = 0;
        //    if (PageSize <= 0)
        //    {
        //        PageSize = 10;
        //    }
        //    string SqlCount = string.Format("select count({0}) from {1}", IndexField, SqlTablesAndWhere);
        //    cmd.CommandText = SqlCount;
        //    RecordCount = Convert.ToInt32(cmd.ExecuteScalar());
        //    if (RecordCount % PageSize == 0)
        //    {
        //        PageCount = RecordCount / PageSize;
        //    }
        //    else
        //    {
        //        PageCount = RecordCount / PageSize + 1;
        //    }
        //    if (PageIndex > PageCount)
        //        PageIndex = PageCount;
        //    if (PageIndex < 1)
        //        PageIndex = 1;
        //    return ReutrnSqlPageSql(connection, cmd, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize);

        //}
        //private string ReutrnSqlPageSql(DbConnection connection, DbCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize)
        //{
        //    if (PageSize <= 0)
        //    {
        //        PageSize = 10;
        //    }
        //    if (PageIndex < 1)
        //        PageIndex = 1;
        //    StringBuilder Sql = new StringBuilder();
        //    Sql.Append("select top ");
        //    Sql.Append(PageSize);
        //    Sql.Append(" ");
        //    Sql.Append(SqlAllFields);
        //    Sql.Append(" from ");
        //    if (PageIndex == 1)
        //    {
        //        Sql.Append(SqlTablesAndWhere);
        //        Sql.Append(" ");
        //        Sql.Append(OrderFields);
        //    }
        //    else
        //    {
        //        if (SqlTablesAndWhere.ToLower().IndexOf(" where ") > 0)
        //        {
        //            string _where = Regex.Replace(SqlTablesAndWhere, @"\ where\ ", " where (", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //            Sql.Append(_where);
        //            Sql.Append(") and (");
        //        }
        //        else
        //        {
        //            Sql.Append(SqlTablesAndWhere);
        //            Sql.Append(" where (");
        //        }
        //        Sql.Append(IndexField);
        //        Sql.Append(" not in (select top ");
        //        Sql.Append((PageIndex - 1) * PageSize);
        //        Sql.Append(" ");
        //        Sql.Append(IndexField);
        //        Sql.Append(" from ");
        //        Sql.Append(SqlTablesAndWhere);
        //        Sql.Append(" ");
        //        Sql.Append(OrderFields);
        //        Sql.Append(")) ");
        //        Sql.Append(OrderFields);
        //    }
        //    return Sql.ToString();
        //}
        ///// <summary>
        ///// 适用SQLite的分页语句
        ///// </summary>
        //private string ReutrnSQLitePageSql(DbConnection connection, DbCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
        //{

        //    /* Sqlite 专用分页Sql */
        //    RecordCount = 0;
        //    PageCount = 0;
        //    if (PageSize <= 0)
        //    {
        //        PageSize = 10;
        //    }
        //    string SqlCount = string.Format("select count({0}) from {1}", IndexField, SqlTablesAndWhere);// "select count(" + IndexField + ") from " + SqlTablesAndWhere;
        //    cmd.CommandText = SqlCount;
        //    RecordCount = Convert.ToInt32(cmd.ExecuteScalar());
        //    if (RecordCount % PageSize == 0)
        //    {
        //        PageCount = RecordCount / PageSize;
        //    }
        //    else
        //    {
        //        PageCount = RecordCount / PageSize + 1;
        //    }
        //    if (PageIndex > PageCount)
        //        PageIndex = PageCount;
        //    if (PageIndex < 1)
        //        PageIndex = 1;

        //    return ReutrnSQLitePageSql(connection, cmd, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize);

        //}
        //private string ReutrnSQLitePageSql(DbConnection connection, DbCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize)
        //{
        //    /* Sqlite 专用分页Sql */
        //    if (PageSize <= 0)
        //    {
        //        PageSize = 10;
        //    }
        //    if (PageIndex < 1)
        //        PageIndex = 1;
        //    StringBuilder Sql = new StringBuilder();
        //    Sql.Append("select ");
        //    Sql.Append(SqlAllFields);
        //    Sql.Append(" from ");
        //    if (PageIndex == 1)
        //    {
        //        Sql.Append(SqlTablesAndWhere);
        //        Sql.Append(" ");
        //        Sql.Append(OrderFields);
        //        Sql.Append(" Limit ");
        //        Sql.Append(PageSize);

        //    }
        //    else
        //    {
        //        Sql.Append(SqlTablesAndWhere);
        //        Sql.Append(" ");
        //        Sql.Append(OrderFields);
        //        Sql.Append(" Limit ");
        //        Sql.Append((PageIndex - 1) * PageSize);
        //        Sql.Append(" , ");
        //        Sql.Append(PageSize);
        //    }
        //    return Sql.ToString();
        //}
        #endregion
        #endregion
        #region other
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void CloseConnection()
        {

            if (this.Connection != null)
            {
                if (this.Connection.State == ConnectionState.Open)
                {
                    this.Connection.Close();
                }
            }
        }

        private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;
            cmd.CommandTimeout = Timeout;
            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                {
                    if (parm != null)
                    {
                        if (parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(parm);
                    }
                }
            }
        }
        #endregion
        #region 参数处理
        public DbParameter[] PrepareParameter(object[] parameters)
        {
            DbParameter[] dbparameters = new DbParameter[parameters.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                dbparameters[i] = CreateParameter();
                dbparameters[i].DbType = PrepareDbType(parameters[i].GetType());
                dbparameters[i].ParameterName = string.Concat("@Param", i.ToString());
                dbparameters[i].Value = parameters[i];
            }
            return dbparameters;

        }

        public string ParseCommandText(string cmdText, object[] parameters)
        {
            if (parameters.Length > 0)
            {
                if (cmdText.Contains("{...}"))
                {
                    string[] array = new string[parameters.Length];
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        array[i] = string.Concat("@Param", i.ToString());
                    }
                    cmdText = cmdText.Replace("{...}", string.Join(",", array));
                }
            }
            if (cmdText.IndexOf('{') != -1)
            {
                cmdText = Regex.Replace(cmdText, @"\{(\d+)\}", "@Param$1");
            }
            return cmdText;
        }

        public DbType PrepareDbType(Type type)
        {
            switch (type.FullName)
            {
                case "System.String":
                    return DbType.String;
                case "System.Byte[]":
                    return DbType.Binary;
                case "System.Byte":
                    return DbType.Byte;
                case "System.Boolean":
                    return DbType.Boolean;
                case "System.DateTime":
                    return DbType.DateTime;
                case "System.Decimal":
                    return DbType.Decimal;
                case "System.Double":
                    return DbType.Double;
                case "System.Guid":
                    return DbType.Guid;
                case "System.Int16":
                    return DbType.Int16;
                case "System.Int32":
                    return DbType.Int32;
                case "System.Int64":
                    return DbType.Int64;
                case "System.SByte":
                    return DbType.SByte;
                case "System.Single":
                    return DbType.Single;
                case "System.UInt16":
                    return DbType.UInt16;
                case "System.UInt32":
                    return DbType.UInt32;
                case "System.UInt64":
                    return DbType.UInt64;
                default:
                    return DbType.Object;
            }

        }
        #endregion
    }
}